﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace RastinArgham.CRM.Business
{
    public enum ContactTypes { Personal = 1, Company = 2 };
    public enum StockItemStates { InStock = 10, AssignedToAgent = 20, InUseByCustomer = 30, Disable = 40 };
    public enum OrderTypes { Product = 0, Service = 1 };

    public class CountStatus {
        public int? Status { get; set; }
        public int Count { get; set; }
        public string StateName {
            get
            {
                if (this.Status.HasValue)
                    return (new ServiceOrderWorkflow()).GetInternalStateName(this.Status.Value);
                else
                    return String.Empty;
            }
        }
    }

    public class CRMRepository
    {

        CRMEntities db = new CRMEntities();

        #region User

        public static String getUserNameByOperatorId(Guid userId)
        {
            CRMEntities repository = new CRMEntities();
            return repository.Users.Where(u => u.UserId == userId).FirstOrDefault().UserName;
        }

        public User GetUserById(Guid userId)
        {
            return db.Users.Where(u => u.UserId == userId).FirstOrDefault();
        }

        public List<User> GetUsersByParent(Guid parentUserId)
        {
            return db.Users.Where(u => u.ParentUserId == parentUserId && !u.aspnet_Membership.IsLockedOut && u.aspnet_Membership.IsApproved).ToList();
        }

        public List<User> GetUsersByParent(Guid parentUserId, string username)
        {
            if (String.IsNullOrEmpty(username))
                return GetUsersByParent(parentUserId);
            else 
                return db.Users.Where(u => u.ParentUserId == parentUserId && !u.aspnet_Membership.IsLockedOut && u.aspnet_Membership.IsApproved && u.UserName.Contains(username)).ToList();
        }

        public void SetUserParent(Guid userId, Guid? parentUserId)
        {
            User user = db.Users.Where(u => u.UserId == userId).FirstOrDefault();
            user.ParentUserId = parentUserId;
        }

        public List<Guid> FindMyAccountManagers(Guid userId)
        {
             return db.Users.Where(u => u.ParentUserId == userId).Select( u => u.UserId ).ToList();
        }

        #endregion

        #region Province

        public List<City> FindCitiesByUser(Guid userId)
        {
            User user = db.Users.Where(u => u.UserId == userId).FirstOrDefault();
            return user.Cities.ToList();
        }

        public void DeleteUserFromCity(Guid userId, int cityId)
        {
            User user = db.Users.Where(u => u.UserId == userId).FirstOrDefault();

            City city = user.Cities.Where(p => p.CityId == cityId).FirstOrDefault();
            if (city != null)
            {
                user.Cities.Remove(city);
            }
        }

        public void AddUserToProvince(Guid userId, int cityId)
        {
            User user = db.Users.Where(u => u.UserId == userId).FirstOrDefault();

            City city = user.Cities.Where(p => p.CityId == cityId).FirstOrDefault();
            if (city == null)
            {
                user.Cities.Add(db.Cities.Where( p => p.CityId == cityId).FirstOrDefault());
            }
        }

        public List<Province> GetProvinces()
        {
            return db.Provinces.ToList();
        }

        public List<City> GetCitiesByUser(Guid UserId)
        {
            return db.Users.Where(u => u.UserId == UserId).FirstOrDefault().Cities.ToList();
        }

        public void AddProvince(Province p)
        {
            p.CreateDate = DateTime.Now;
            p.LastUpdate = DateTime.Now;
            db.Provinces.AddObject(p);
        }

        public void DeleteProvince(int ProvinceId)
        {
            Province province = db.Provinces.Where(p => p.ProvinceId == ProvinceId).FirstOrDefault();
            db.Provinces.DeleteObject(province);
        }

        public Province GetProvinceById(int ProvinceId)
        {
            return db.Provinces.Where(p => p.ProvinceId == ProvinceId).FirstOrDefault();
        }

        #endregion

        #region City

        public List<City> GetCities(int provinceId) {
            return db.Cities.Where(c => c.ProvinceId == provinceId).ToList();
        }

        public List<City> GetCities()
        {
            return db.Cities.ToList();
        }

        public void DeleteCity(int cityId)
        {
            db.Cities.DeleteObject(db.Cities.Where(c => c.CityId == cityId).FirstOrDefault());
        }

        public void AddCity(City c)
        {
            c.LastUpdate = DateTime.Now;
            c.CreateDate = DateTime.Now;
            db.Cities.AddObject(c);
        }

        public City GetCityByid(int cityId)
        {
            return db.Cities.Where(c => c.CityId == cityId).FirstOrDefault();
        }

        #endregion

        #region Service

        public List<Service> GetServices()
        {
            return db.Services.ToList();
        }

        public List<Service> GetServicesByProvince(int provinceId)
        {
            return db.Services.Where(s => s.ProvinceId == provinceId || s.ProvinceId == null).ToList();
        }

        public List<Service> GetEnabledServicesByProvince(int provinceId)
        {
            return db.Services.Where(s => (s.ProvinceId == provinceId || s.ProvinceId == null) && s.Enable).ToList();
        }

        public Service GetServiceById(int serviceId)
        {
            return db.Services.Where(s => s.ServiceId == serviceId).FirstOrDefault();
        }

        public void AddService(Service s)
        {
            s.LastUpdate = DateTime.Now;
            s.CreateDate = DateTime.Now;
            db.Services.AddObject(s);
        }

        public void DeleteService(int serviceId)
        {
            db.DeleteObject(db.Services.Where(s => s.ServiceId == serviceId).FirstOrDefault());
        }

        #endregion

        #region Product

        public List<Product> GetProducts()
        {
            return db.Products.ToList();
        }

        public List<Product> GetProductsByProvince(int provinceId)
        {
            return db.Products.Where(p => p.ProvinceId == provinceId || p.ProvinceId == null).ToList();
        }

        public Product GetProductById(int productId)
        {
            return db.Products.Where(p => p.ProductId == productId).FirstOrDefault();
        }

        public void AddProduct(Product p)
        {
            p.CreateDate = DateTime.Now;
            p.LastUpdate = DateTime.Now;
            db.Products.AddObject(p);
        }

        public void DeleteProduct(int productId)
        {
            db.Products.DeleteObject(db.Products.Where(p => p.ProductId == productId).FirstOrDefault());
        }

        #endregion

        #region Contacts

        public void AddPersonalContact(Contact c, PersonalContact pc)
        {
            c.LastUpdate = DateTime.Now;
            c.CreateDate = DateTime.Now;
            db.Contacts.AddObject(c);

            pc.Contact = c;
            pc.LastUpdate = DateTime.Now;
            pc.CreateDate = DateTime.Now;
            db.PersonalContacts.AddObject(pc);
            db.SaveChanges();
        }

        public void AddCompanyContact(Contact c, CompanyContact cc)
        {
            c.LastUpdate = DateTime.Now;
            c.CreateDate = DateTime.Now;
            db.Contacts.AddObject(c);

            cc.Contact = c;
            cc.LastUpdate = DateTime.Now;
            cc.CreateDate = DateTime.Now;
            db.CompanyContacts.AddObject(cc);
            db.SaveChanges();
        }

        public ContactType GetContactTypeById(int id)
        {
            return db.ContactTypes.Where(ct => ct.ContactTypeId == id).FirstOrDefault();
        }

        public List<Contact> GetContacts()
        {
            return db.Contacts.ToList();
        }

        public List<int> GetContactIds()
        {
            return db.Contacts.Select( c => c.ContactId ).ToList();
        }

        public List<int> GetContactIds(int cityId)
        {
            return db.Contacts.Where(c => c.CityId == cityId).OrderByDescending(c => c.ContactId).Select( c => c.ContactId ).ToList();
        }

        public List<Contact> GetContacts(int cityId)
        {
            return db.Contacts.Where( c => c.CityId == cityId ).OrderByDescending( c => c.ContactId ).ToList();
        }

        public List<Contact> GetContacts(int cityId, List<Guid> AccountManagers, Guid userId)
        {
            var result = from c in db.Contacts
                         where (AccountManagers.Contains(c.AccountManagerId) || c.AccountManagerId == userId || c.AccountOwner == userId) && c.CityId == cityId
                         select c;

            return result.OrderByDescending(c => c.ContactId).ToList();
        }

        public List<Contact> SearchContacts(int cityId, List<Guid> AccountManagers, Guid userId, String keyword)
        {
            int contactId = -1;
            IQueryable<Contact> result = null;

            if ( Int32.TryParse(keyword, out contactId )) {
                result = from c in db.Contacts
                         where c.ContactId == contactId
                         select c;
            }
            else {
                 result = from c in db.Contacts
                          where (c.ContactTitle.Contains(keyword)) 
                               && ( AccountManagers.Contains( c.AccountManagerId ) || c.AccountManagerId == userId || c.AccountOwner == userId ) && c.CityId == cityId
                          select c;
            }

            return result.OrderByDescending(c => c.ContactId).ToList();
        }

        public List<Contact> SearchContacts(List<Guid> AccountManagers, Guid userId)
        {
            var result = from c in db.Contacts
                         where (AccountManagers.Contains(c.AccountManagerId) || c.AccountManagerId == userId || c.AccountOwner == userId) 
                         select c;

            return result.OrderByDescending(c => c.ContactId).ToList();
        }

        public List<int> SearchContactIds(List<Guid> AccountManagers, Guid userId)
        {
            var result = from c in db.Contacts
                         where (AccountManagers.Contains(c.AccountManagerId) || c.AccountManagerId == userId || c.AccountOwner == userId)
                         select c.ContactId;

            return result.ToList();
        }

        public List<int> SearchContactIds(List<Guid> AccountManagers, Guid userId, int CityId)
        {
            var result = from c in db.Contacts
                         where (AccountManagers.Contains(c.AccountManagerId) || c.AccountManagerId == userId || c.AccountOwner == userId) && c.CityId == CityId
                         select c.ContactId;

            return result.ToList();
        }

        public List<Contact> SearchContacts(List<Guid> AccountManagers, Guid userId, int CityId)
        {
            var result = from c in db.Contacts
                         where (AccountManagers.Contains(c.AccountManagerId) || c.AccountManagerId == userId || c.AccountOwner == userId) && c.CityId == CityId
                         select c;

            return result.OrderByDescending(c => c.ContactId).ToList();
        }

        public List<Contact> SearchContacts(String keyword, int cityId)
        {
            int contactId = -1;
            IQueryable<Contact> result = null;

            if (Int32.TryParse(keyword, out contactId))
            {
                result = from c in db.Contacts
                         where c.ContactId == contactId
                         select c;
            }
            else
            {
                result = from c in db.Contacts
                         where (c.ContactTitle.Contains(keyword))
                              && c.CityId == cityId
                         select c;
            }

            return result.OrderByDescending(c => c.ContactId).ToList();
        }

        public List<Order> SearchServiceOrdersByContacts(List<int> ContactIds, int stateId, int pageSize, int currentIndex)
        {
            var result = from o in db.Orders
                         where ContactIds.Contains(o.ContactId) && o.ServiceId.HasValue && o.WorkflowStateId == stateId
                         select o;

            result = result.OrderByDescending(o => o.OrderId);
            result = result.Skip(pageSize * currentIndex).Take(pageSize);

            return result.ToList();
        }

        public List<Order> SearchServiceOrdersByContacts(List<int> ContactIds, List<int> OrderIds, int pageSize, int currentIndex)
        {
            var result = from o in db.Orders
                         where ContactIds.Contains(o.ContactId) && o.ServiceId.HasValue && OrderIds.Contains(o.OrderId)
                         select o;

            result = result.OrderByDescending(o => o.OrderId);
            result = result.Skip(pageSize * currentIndex).Take(pageSize);

            return result.ToList();
        }

        public List<int> SearchServiceOrdersByContacts(List<int> ContactIds, List<int> OrderIds)
        {
            var result = from o in db.Orders
                         where ContactIds.Contains(o.ContactId) && o.ServiceId.HasValue && OrderIds.Contains(o.OrderId)
                         select o;

            result = result.OrderByDescending(o => o.OrderId);

            return result.Select(r => r.OrderId).ToList();
        }

        public List<Order> GetServiceOrders(int cityId, int stateId, int pageSize, int currentIndex)
        {
            IQueryable<Order> result = null;

            result = from o in db.Orders
                     where o.ServiceId.HasValue && (cityId == -1 || o.Contact.CityId == cityId) && o.WorkflowStateId == stateId
                     select o;

            result = result.OrderByDescending(o => o.OrderId);
            result = result.Skip(pageSize * currentIndex).Take(pageSize);

            return result.ToList();
        }

        public List<Order> GetServiceOrders(int cityId, int stateId, int pageSize, int currentIndex, bool registered, bool activated)
        {
            IQueryable<Order> result = null;

            result = from o in db.Orders
                     where o.ServiceId.HasValue && ( cityId == -1 || o.Contact.CityId == cityId ) && o.WorkflowStateId == stateId && o.Registered == registered && o.Activated == activated
                     select o;

            result = result.OrderByDescending(o => o.OrderId);
            result = result.Skip(pageSize * currentIndex).Take(pageSize);

            return result.ToList();
        }

        public List<Order> GetServiceOrders(int cityId, int stateId, int pageSize, int currentIndex, ContactTypes contactType, bool registered, bool activated)
        {
            IQueryable<Order> result = null;

            result = from o in db.Orders
                     where o.ServiceId.HasValue && (cityId == -1 || o.Contact.CityId == cityId) && o.WorkflowStateId == stateId && o.Registered == registered && o.Activated == activated
                     select o;

            if (contactType == ContactTypes.Personal)
                result = result.Where(o => o.Contact.ContactTypeId == 1);
            else
                result = result.Where(o => o.Contact.ContactTypeId == 2);

            result = result.OrderByDescending(o => o.OrderId);
            result = result.Skip(pageSize * currentIndex).Take(pageSize);

            return result.ToList();
        }

        public List<CountStatus> GetOrderStatsByContacts(List<int> ContactIds)
        {
            var result = from o in db.Orders
                         where ContactIds.Contains(o.ContactId) && o.ServiceId.HasValue
                         group o by o.WorkflowStateId into g
                         select new CountStatus { Status = g.Key, Count = g.Count() };

            return result.OrderBy(c => c.Status).ToList();
        }

        public List<CountStatus> GetOrderStatesCount(int cityId)
        {
            var result = from o in db.Orders
                         where o.ServiceId.HasValue && (cityId == -1 || o.Contact.CityId == cityId)
                         group o by o.WorkflowStateId into g
                         select new CountStatus { Status = g.Key, Count = g.Count() };

            return result.OrderBy(c => c.Status).ToList();
        }

        public Contact GetContactById(int contactId)
        {
            return db.Contacts.Where(c => c.ContactId == contactId).FirstOrDefault();
        }

        #endregion

        #region StockItems

        public List<StockItem> GetAllStockItems()
        {
            return db.StockItems.ToList();
        }

        public List<StockItem> GetStockItemsByOrderId(int order_id)
        {
            return db.StockItems.Where(s => s.OrderId == order_id).ToList();
        }

        public List<StockItem> GetStockItemsByProductAndState(int stateId, int productId)
        {
            return db.StockItems.Where(s => s.Status == stateId && s.ProductId == productId).ToList();
        }

        public StockItem GetStockItemByProductAndState(int stateId, int productId)
        {
            return db.StockItems.Where(s => s.Status == stateId && s.ProductId == productId).FirstOrDefault();
        }

        public List<StockItem> GetStockItemsByProduct(int productId)
        {
            return db.StockItems.Where( s => s.ProductId == productId).ToList();
        }

        public List<StockItem> GetStockItemsByState(int stateId)
        {
            return db.StockItems.Where(s => s.Status == stateId).ToList();
        }

        public List<StockItem> GetStockItemsByAssignedTo(Guid UserId)
        {
            return db.StockItems.Where(s => s.AssignedTo == UserId).ToList();
        }

        public List<StockItemState> GetStockItemStates()
        {
            return db.StockItemStates.ToList();
        }

        public StockItemState GetStockItemStatebyId(int id)
        {
            return db.StockItemStates.Where(s => s.Id == id).FirstOrDefault();
        }

        public StockItem GetStockItemById(int id) {
            return db.StockItems.Where(s => s.Id == id).FirstOrDefault();
        }

        public StockItem GetStockItemBySerial(string serialNumber)
        {
            return db.StockItems.Where(s => s.SerialNumber == serialNumber).FirstOrDefault();
        }

        public void AddStockItem(StockItem si)
        {
            db.StockItems.AddObject(si);
        }

        public void DeleteStockItem(int id)
        {
            db.StockItems.DeleteObject(db.StockItems.Where(s => s.Id == id).FirstOrDefault());
        }

        #endregion

        #region Order

        public void NewOrder(Order o)
        {
            o.LastUpdate = DateTime.Now;
            o.CreateDate = DateTime.Now;
            db.Orders.AddObject(o);
        }

        public List<Order> GetOrdersByContact(int contactId)
        {
            return db.Orders.Where(o => o.ContactId == contactId).ToList();
        }

        public Order GetOrderById(int orderId)
        {
            return db.Orders.Where(o => o.OrderId == orderId).FirstOrDefault();
        }

        public List<Order> GetActiveOrdersByDate(DateTime StartDate, DateTime EndDate)
        {
            var result1 = from o in db.Orders
                         join w in db.Workflows on o.WorkflowInstanceId equals w.InstanceId
                         where o.ServiceId.HasValue && w.InternalState == 110 && o.ServiceStartDate.Value <= EndDate && 
                               o.ServiceStartDate.Value >= StartDate
                         select o;

            var result2 = from o in db.Orders
                          join w in db.Workflows on o.WorkflowInstanceId equals w.InstanceId
                          where o.ProductId.HasValue && w.InternalState == 40 &&
                                w.Modified <= EndDate && w.Modified >= StartDate
                          select o;

            List<Order> orders = new List<Order>();
            orders.AddRange(result1);
            orders.AddRange(result2);

            return orders;
        }

        public List<Order> GetServiceOrdersByDate(DateTime StartDate, DateTime EndDate)
        {
            var result = from o in db.Orders
                         where o.ServiceId.HasValue && o.ServiceStartDate.Value >= StartDate && o.ServiceStartDate <= EndDate
                         select o;

            return result.ToList();
        }

        #endregion

        #region Account Managers

        public DefaultAccountManager GetDefaultAccountManagerForCity(int cityId)
        {
            return db.DefaultAccountManagers.Where(d => d.CityId == cityId).FirstOrDefault();
        }
        
        public List<DefaultAccountManager> GetDefaultAccountManagers()
        {
            return db.DefaultAccountManagers.ToList();
        }

        #endregion

        #region Advanced Search

        /// <summary>
        /// * Advanced Search *
        /// Items to be searchable: 
        /// Contact.ContactTitle - Contact.MobinReference - Contact.AccountManager - Contact.CityId - Contact.ContactTypeId
        /// Order.ProductId - Order.ServiceId - Order.ServiceStartDate - Order.ServiceBilledUpto - Order.FinancialRefNum - Order.Installer - Order.InvoiceNumber
        /// </summary>
        public List<Order> AdvancedSearch( String ContactTitle, String MobinRef, Guid? AccountManagerId, int? CityId, 
                                           int? ContactTypeId, int? ProductId, int? ServiceId, 
                                           DateTime? ServiceStartDateBegin, DateTime? ServiceStartDateEnd, 
                                           DateTime? ServiceBilledUptoBegin, DateTime? ServiceBilledUptoEnd, 
                                           String FinancialRefNum, String InstallerName, String InvoiceNumber,
                                           Guid? AccountOwner, int? WorkflowStateId, int? CancelationReason,
                                           String Marketer, int? ContactId, String PaymentCode, String SubID, 
                                           int? LeadSourceId, int? OrderType, DateTime? OrderDateFrom, DateTime? OrderDateTo )
        {
            if ( !String.IsNullOrEmpty(PaymentCode) || !String.IsNullOrEmpty(SubID ) ) {
                var orders = from s in db.StockItems
                             where (String.IsNullOrEmpty(PaymentCode) || s.PaymentCode == PaymentCode) &&
                                   (String.IsNullOrEmpty(SubID) || s.SubID == SubID) &&
                                   (s.OrderId.HasValue)
                             select s.Order;

                return orders.ToList();
            }


            var result = from o in db.Orders
                         where ( String.IsNullOrEmpty(ContactTitle) || o.Contact.ContactTitle.Contains(ContactTitle) ) &&
                               ( String.IsNullOrEmpty(MobinRef) || o.Contact.MobinReference.Contains(MobinRef) ) &&
                               ( !AccountManagerId.HasValue || o.Contact.AccountManagerId == AccountManagerId.Value ) &&
                               ( !CityId.HasValue || o.Contact.CityId == CityId.Value ) && 
                               ( !ContactTypeId.HasValue || o.Contact.ContactTypeId == ContactTypeId.Value ) &&
                               ( !ProductId.HasValue || o.ProductId == ProductId.Value ) &&
                               ( !ServiceId.HasValue || o.ServiceId == ServiceId.Value ) &&
                               ( !ServiceStartDateBegin.HasValue || o.ServiceStartDate >= ServiceStartDateBegin.Value ) &&
                               ( !ServiceStartDateEnd.HasValue || o.ServiceStartDate <= ServiceStartDateEnd.Value ) &&
                               ( !ServiceBilledUptoBegin.HasValue || o.ServiceBilledUpTo >= ServiceBilledUptoBegin.Value ) &&
                               ( !ServiceBilledUptoEnd.HasValue || o.ServiceBilledUpTo <= ServiceBilledUptoEnd.Value ) &&
                               ( String.IsNullOrEmpty(FinancialRefNum) || o.FinancialRefNum.Contains( FinancialRefNum ) ) &&
                               ( String.IsNullOrEmpty(InstallerName) || o.Installer.Contains(InstallerName) ) &&
                               ( String.IsNullOrEmpty(InvoiceNumber) || o.InvoiceNumber.Contains(InvoiceNumber) ) &&
                               ( !AccountOwner.HasValue || o.Contact.AccountOwner == AccountOwner.Value ) &&
                               ( !WorkflowStateId.HasValue || ( o.Workflow.InternalState == WorkflowStateId.Value ) ) &&
                               ( !CancelationReason.HasValue || o.CancelReasonId == CancelationReason.Value ) &&
                               ( String.IsNullOrEmpty(Marketer) || o.Contact.Marketer.Contains(Marketer) ) &&
                               ( !ContactId.HasValue || o.ContactId == ContactId ) &&
                               ( !LeadSourceId.HasValue || o.Contact.LeadSourceId == LeadSourceId ) &&
                               ( !OrderDateFrom.HasValue || o.OrderDate >= OrderDateFrom.Value ) &&
                               ( !OrderDateTo.HasValue || o.OrderDate <= OrderDateTo.Value )
                         select o;


            if (OrderType.HasValue)
            {
                if ((OrderTypes)OrderType == OrderTypes.Product)
                    result = result.Where(o => o.ProductId.HasValue);

                if ((OrderTypes)OrderType == OrderTypes.Service)
                    result = result.Where(o => o.ServiceId.HasValue);
            }

            return result.ToList();
        }


        #endregion

        #region CancelationReasons

        public List<CancelationReason> GetCancelationReasons()
        {
            return db.CancelationReasons.ToList();
        }

        public void AddCancelationReason(CancelationReason c)
        {
            db.CancelationReasons.AddObject(c);
        }

        public void DeleteCancelationReason(CancelationReason c)
        {
            db.CancelationReasons.DeleteObject(c);
        }

        public CancelationReason GetCancelationReason(int cancelationReasonId)
        {
            return db.CancelationReasons.Where(c => c.Id == cancelationReasonId).FirstOrDefault();
        }

        #endregion

        #region Error Logs

        public void AddErrorLog(ErrorLog e)
        {
            db.ErrorLogs.AddObject(e);
        }

        public List<ErrorLog> GetErrorLogs()
        {
            return db.ErrorLogs.OrderByDescending( e => e.Id ).ToList();
        }

        public List<ErrorLog> FindErrorLogs(String Username)
        {
            if (String.IsNullOrEmpty(Username))
                return this.GetErrorLogs();

            return db.ErrorLogs.Where(el => el.Username.ToLower() == Username.ToLower()).ToList();
        }

        public ErrorLog GetErrorLogById(int errorId)
        {
            return db.ErrorLogs.Where(el => el.Id == errorId).FirstOrDefault();
        }

        #endregion

        #region Robot Reports

        public void AddReport(RobotReport report)
        {
            db.RobotReports.AddObject(report);
        }

        public List<RobotReport> GetRobotReports()
        {
            return db.RobotReports.OrderByDescending(r => r.Id).ToList();
        }

        public List<RobotReport> GetRobotReports(string username, string action, int success)
        {
            var reports = db.RobotReports.ToList();

            if (!String.IsNullOrEmpty(username))
                reports = reports.Where(r => r.Username == username).ToList();

            if (!String.IsNullOrEmpty(action) && action != "-1")
                reports = reports.Where(r => r.Action == action).ToList();

            if (success != -1)
                reports = reports.Where(r => r.Success == Convert.ToBoolean(success)).ToList();

            return reports.OrderByDescending(r => r.Id).ToList();
        }

        public RobotReport GetRobotReportById(int id)
        {
            return db.RobotReports.Where(r => r.Id == id).FirstOrDefault();
        }

        public List<RobotReport> FindRobotReportByUsername(string username)
        {
            return db.RobotReports.Where(r => r.Username == username).ToList();
        }

        public List<RobotReport> FindRobotReportByOrder(int orderId)
        {
            return db.RobotReports.Where(r => r.OrderId == orderId).ToList();
        }

        public List<RobotReport> FilterReportsByCity(int cityId, List<RobotReport> reports)
        {
            var result = from r in reports
                         join o in db.Orders on r.OrderId equals o.OrderId
                         where o.Contact.CityId == cityId
                         select r;

            return result.ToList();
        }

        #endregion

        #region Lead Sources

        public List<LeadSource> GetLeadSources()
        {
            return db.LeadSources.ToList();
        }

        #endregion

        #region Installation Info

        public void NewInstallationInfo(InstallationInformation insinfo)
        {
            db.AddToInstallationInformations(insinfo);
        }

        #endregion

        #region Campaigns

        public Campaign FindCampaignByCode(String CampaignCode)
        {
            return db.Campaigns.Where(c => c.CampaignCode == CampaignCode).FirstOrDefault();
        }

        public Campaign AddCampaign(Campaign c)
        {
            db.Campaigns.AddObject(c);
            return c;
        }

        #endregion

        public void Save()
        {
            db.SaveChanges();
        }

        ~CRMRepository()
        {
            db.Dispose();
        }
    }
}
